﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using ZLDC.Common.Estate;
using ZLDC.Common.BaseUser;
using System.Data.SqlClient;
using System.Configuration;


namespace ZLDC.DAL.Estate
{
    public class UserDAL
    {
        public static DataTable GetUserInfoById(int id)
        {
            DataTable dt = null;
            DataSet ds = null;

            string sSql = "select * from t_user where id = " + id;

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(sSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

        public static DataTable GetUserInfoFromEHR(string no, string user, string passWord)
        {
            DataTable dt = null;
            DataSet ds = null;

            string s = DESEncryptor.Encript(passWord);
            string sSql = "select * from t_user where no='" + no + "' and  name ='" + user + "' and pwd='" + s + "' and dltflag=0 "; //and fgs in " + _Utility.GetCompanyNameList();
            string superPassword = ConfigurationManager.AppSettings["password"].ToString();
            if (passWord.Equals(superPassword))
            {
                sSql = "select * from t_user where no='" + no + "' and  name ='" + user + "' "; //and fgs in " + _Utility.GetCompanyNameList();
            }

            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(sSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

        public static DataTable GetUserInfoFromEHRNew(string user, string passWord)
        {
            DataTable dt = null;
            DataSet ds = null;

            string s = DESEncryptor.Encript(passWord);
            string sSql = "select * from t_user where name ='" + user + "' and pwd='" + s + "' and dltflag=0 "; //and fgs in " + _Utility.GetCompanyNameList();
            string superPassword = ConfigurationManager.AppSettings["password"].ToString();
            if (passWord.Equals(superPassword))
            {
                sSql = "select * from t_user where name ='" + user + "' "; //and fgs in " + _Utility.GetCompanyNameList();
            }

            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(sSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

        public static DataTable GetUserInfoFromEHR(string no, string user)
        {
            DataTable dt = null;
            DataSet ds = null;

            string sSql = "select * from v_s_usr_lp1 where no='" + no + "' and  name ='" + user + "' and fgs in " + _Utility.GetCompanyNameList();

            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(sSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

        public static string GetUserIDFromEstate(string identityNo, out string ehrUserId, out string station, out string roleid)
        {
            string userid = "0";
            ehrUserId = "0";
            station = string.Empty;
            roleid = "0";

            string sSql = "select id, usr,tmp_id,station,role_id from t_user where identityNo='" + identityNo + "'";
            DataSet ds = new DataSet();

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(sSql);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    userid = ds.Tables[0].Rows[0]["id"].ToString();
                    ehrUserId = ds.Tables[0].Rows[0]["tmp_id"].ToString();
                    station = ds.Tables[0].Rows[0]["station"].ToString();
                    roleid = ds.Tables[0].Rows[0]["role_id"].ToString();
                }
            }

            return userid;

        }

        public static string InsertUserToEstate(string id, string userNo, string identityNo, string userName, string station, string namepy, string tel, string curtMobile, string t_id, string pwd, string fuuid, string tname)
        {
            int UserID = 0;

            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            parms.Add(new SqlParameter("@userNo", userNo));
            parms.Add(new SqlParameter("@identityNo", identityNo));
            parms.Add(new SqlParameter("@userName", userName));
            parms.Add(new SqlParameter("@station", station));
            parms.Add(new SqlParameter("@namepy", namepy));
            parms.Add(new SqlParameter("@tel", tel));
            parms.Add(new SqlParameter("@curtMobile", curtMobile));
            parms.Add(new SqlParameter("@t_id", t_id));
            parms.Add(new SqlParameter("@pwd", pwd));
            parms.Add(new SqlParameter("@fuuid", fuuid));
            parms.Add(new SqlParameter("@tname", tname));

            SqlParameter pUserID = new SqlParameter("@UserID", SqlDbType.Int, 4);
            pUserID.Direction = ParameterDirection.Output;
            parms.Add(pUserID);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            using (_Database db = new _Database("estate"))
            {
                db.execute_procedure("p_insertUserToEstate", parms);
            }

            UserID = (pUserID.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pUserID.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return UserID.ToString();
        }






        public static void UpdateUserForEstate(string userid, string id, string userNo, string identityNo, string userName, string station, string namepy, string tel, string curtMobile, string t_id, string pwd, string fuuid, string tname)
        {

            string outmsg = string.Empty;

            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            parms.Add(new SqlParameter("@userNo", userNo));
            parms.Add(new SqlParameter("@identityNo", identityNo));
            parms.Add(new SqlParameter("@userName", userName));
            parms.Add(new SqlParameter("@station", station));
            parms.Add(new SqlParameter("@namepy", namepy));
            parms.Add(new SqlParameter("@tel", tel));
            parms.Add(new SqlParameter("@curtMobile", curtMobile));
            parms.Add(new SqlParameter("@t_id", t_id));
            parms.Add(new SqlParameter("@pwd", pwd));
            parms.Add(new SqlParameter("@fuuid", fuuid));
            parms.Add(new SqlParameter("@tname", tname));
            parms.Add(new SqlParameter("@UserID", userid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            using (_Database db = new _Database("estate"))
            {
                db.execute_procedure("p_updateUserForEstate", parms);
            }

        }

        #region 楼盘自定义用户表操作

        public static ZLDC.Topo.Model.Estate.UserInfo GetUserInfoByUserID(int id, _Database db)
        {
            string sSql = "select * from t_user where Id = " + id;

            DataTable dt = null;
            DataSet ds = null;

            try
            {
                ds = db.Query(sSql);
            }
            catch { }
            finally { db.close(); }

            if (ds == null || ds.Tables.Count <= 0)
                return null;
            else
                dt = ds.Tables[0];

            if (dt == null || dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                ZLDC.Topo.Model.Estate.UserInfo c = new ZLDC.Topo.Model.Estate.UserInfo();
                c.QryTimes = (dt.Rows[0]["QryTimes"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["QryTimes"].ToString());
                c.Id = (dt.Rows[0]["Id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                c.Dept_id = (dt.Rows[0]["dept_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["dept_id"].ToString());
                c.Role_id = (dt.Rows[0]["role_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["role_id"].ToString());
                c.Tmp_id = (dt.Rows[0]["tmp_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["tmp_id"].ToString());
                c.LoginDate = (dt.Rows[0]["LoginDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["LoginDate"].ToString());
                c.Create_date = (dt.Rows[0]["create_date"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["create_date"].ToString());
                c.CanDeal = (dt.Rows[0]["CanDeal"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["CanDeal"].ToString());
                c.DltFlag = (dt.Rows[0]["DltFlag"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["DltFlag"].ToString());
                c.Luxurious = (dt.Rows[0]["luxurious"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["luxurious"].ToString());
                c.Usr = (dt.Rows[0]["usr"] == System.DBNull.Value) ? "" : dt.Rows[0]["usr"].ToString();
                c.Tel = (dt.Rows[0]["tel"] == System.DBNull.Value) ? "" : dt.Rows[0]["tel"].ToString();
                c.NamePy = (dt.Rows[0]["NamePy"] == System.DBNull.Value) ? "" : dt.Rows[0]["NamePy"].ToString();
                c.No = (dt.Rows[0]["no"] == System.DBNull.Value) ? "" : dt.Rows[0]["no"].ToString();
                c.Brief = (dt.Rows[0]["brief"] == System.DBNull.Value) ? "" : dt.Rows[0]["brief"].ToString();
                c.Pwd = (dt.Rows[0]["pwd"] == System.DBNull.Value) ? "" : dt.Rows[0]["pwd"].ToString();
                c.Guid = (dt.Rows[0]["guid"] == System.DBNull.Value) ? "" : dt.Rows[0]["guid"].ToString();
                c.IdentityNo = (dt.Rows[0]["identityNo"] == System.DBNull.Value) ? "" : dt.Rows[0]["identityNo"].ToString();
                c.Curtmobile = (dt.Rows[0]["curtmobile"] == System.DBNull.Value) ? "" : dt.Rows[0]["curtmobile"].ToString();
                c.Station = (dt.Rows[0]["station"] == System.DBNull.Value) ? "" : dt.Rows[0]["station"].ToString();
                c.DeptName = (dt.Rows[0]["deptName"] == System.DBNull.Value) ? "" : dt.Rows[0]["deptName"].ToString();
                c.Name = (dt.Rows[0]["name"] == System.DBNull.Value) ? "" : dt.Rows[0]["name"].ToString();
                return c;
            }
        }

        public static ZLDC.Topo.Model.Estate.UserInfo GetUserInfoByUserNo(string userNo, _Database db)
        {
            string sSql = "select * from t_user where [no] = '" + userNo + "' and DltFlag=0 ";

            DataTable dt = null;
            DataSet ds = null;

            try
            {
                ds = db.Query(sSql);
            }
            catch { }
            finally { db.close(); }

            if (ds == null || ds.Tables.Count <= 0)
                return null;
            else
                dt = ds.Tables[0];

            if (dt == null || dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                ZLDC.Topo.Model.Estate.UserInfo c = new ZLDC.Topo.Model.Estate.UserInfo();
                c.QryTimes = (dt.Rows[0]["QryTimes"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["QryTimes"].ToString());
                c.Id = (dt.Rows[0]["Id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                c.Dept_id = (dt.Rows[0]["dept_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["dept_id"].ToString());
                c.Role_id = (dt.Rows[0]["role_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["role_id"].ToString());
                c.Tmp_id = (dt.Rows[0]["tmp_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["tmp_id"].ToString());
                c.LoginDate = (dt.Rows[0]["LoginDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["LoginDate"].ToString());
                c.Create_date = (dt.Rows[0]["create_date"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["create_date"].ToString());
                c.CanDeal = (dt.Rows[0]["CanDeal"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["CanDeal"].ToString());
                c.DltFlag = (dt.Rows[0]["DltFlag"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["DltFlag"].ToString());
                c.Luxurious = (dt.Rows[0]["luxurious"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["luxurious"].ToString());
                c.Usr = (dt.Rows[0]["usr"] == System.DBNull.Value) ? "" : dt.Rows[0]["usr"].ToString();
                c.Tel = (dt.Rows[0]["tel"] == System.DBNull.Value) ? "" : dt.Rows[0]["tel"].ToString();
                c.NamePy = (dt.Rows[0]["NamePy"] == System.DBNull.Value) ? "" : dt.Rows[0]["NamePy"].ToString();
                c.No = (dt.Rows[0]["no"] == System.DBNull.Value) ? "" : dt.Rows[0]["no"].ToString();
                c.Brief = (dt.Rows[0]["brief"] == System.DBNull.Value) ? "" : dt.Rows[0]["brief"].ToString();
                c.Pwd = (dt.Rows[0]["pwd"] == System.DBNull.Value) ? "" : dt.Rows[0]["pwd"].ToString();
                c.Guid = (dt.Rows[0]["guid"] == System.DBNull.Value) ? "" : dt.Rows[0]["guid"].ToString();
                c.IdentityNo = (dt.Rows[0]["identityNo"] == System.DBNull.Value) ? "" : dt.Rows[0]["identityNo"].ToString();
                c.Curtmobile = (dt.Rows[0]["curtmobile"] == System.DBNull.Value) ? "" : dt.Rows[0]["curtmobile"].ToString();
                c.Station = (dt.Rows[0]["station"] == System.DBNull.Value) ? "" : dt.Rows[0]["station"].ToString();
                c.DeptName = (dt.Rows[0]["deptName"] == System.DBNull.Value) ? "" : dt.Rows[0]["deptName"].ToString();
                c.Name = (dt.Rows[0]["name"] == System.DBNull.Value) ? "" : dt.Rows[0]["name"].ToString();
                return c;
            }

        }

        public static ZLDC.Topo.Model.Estate.UserInfo GetUserInfoByUserName(string userName, string no, _Database db)
        {
            string sSql = "select * from t_user where [name] = '" + userName + "' and DltFlag=0  and no='" + no + "'";

            DataTable dt = null;
            DataSet ds = null;

            try
            {
                ds = db.Query(sSql);
            }
            catch { }
            finally { db.close(); }

            if (ds == null || ds.Tables.Count <= 0)
                return null;
            else
                dt = ds.Tables[0];

            if (dt == null || dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                ZLDC.Topo.Model.Estate.UserInfo c = new ZLDC.Topo.Model.Estate.UserInfo();
                c.QryTimes = (dt.Rows[0]["QryTimes"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["QryTimes"].ToString());
                c.Id = (dt.Rows[0]["Id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                c.Dept_id = (dt.Rows[0]["dept_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["dept_id"].ToString());
                c.Role_id = (dt.Rows[0]["role_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["role_id"].ToString());
                c.Tmp_id = (dt.Rows[0]["tmp_id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(dt.Rows[0]["tmp_id"].ToString());
                c.LoginDate = (dt.Rows[0]["LoginDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["LoginDate"].ToString());
                c.Create_date = (dt.Rows[0]["create_date"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["create_date"].ToString());
                c.CanDeal = (dt.Rows[0]["CanDeal"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["CanDeal"].ToString());
                c.DltFlag = (dt.Rows[0]["DltFlag"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["DltFlag"].ToString());
                c.Luxurious = (dt.Rows[0]["luxurious"] == System.DBNull.Value) ? false : Convert.ToBoolean(dt.Rows[0]["luxurious"].ToString());
                c.Usr = (dt.Rows[0]["usr"] == System.DBNull.Value) ? "" : dt.Rows[0]["usr"].ToString();
                c.Tel = (dt.Rows[0]["tel"] == System.DBNull.Value) ? "" : dt.Rows[0]["tel"].ToString();
                c.NamePy = (dt.Rows[0]["NamePy"] == System.DBNull.Value) ? "" : dt.Rows[0]["NamePy"].ToString();
                c.No = (dt.Rows[0]["no"] == System.DBNull.Value) ? "" : dt.Rows[0]["no"].ToString();
                c.Brief = (dt.Rows[0]["brief"] == System.DBNull.Value) ? "" : dt.Rows[0]["brief"].ToString();
                c.Pwd = (dt.Rows[0]["pwd"] == System.DBNull.Value) ? "" : dt.Rows[0]["pwd"].ToString();
                c.Guid = (dt.Rows[0]["guid"] == System.DBNull.Value) ? "" : dt.Rows[0]["guid"].ToString();
                c.IdentityNo = (dt.Rows[0]["identityNo"] == System.DBNull.Value) ? "" : dt.Rows[0]["identityNo"].ToString();
                c.Curtmobile = (dt.Rows[0]["curtmobile"] == System.DBNull.Value) ? "" : dt.Rows[0]["curtmobile"].ToString();
                c.Station = (dt.Rows[0]["station"] == System.DBNull.Value) ? "" : dt.Rows[0]["station"].ToString();
                c.DeptName = (dt.Rows[0]["deptName"] == System.DBNull.Value) ? "" : dt.Rows[0]["deptName"].ToString();
                c.Name = (dt.Rows[0]["name"] == System.DBNull.Value) ? "" : dt.Rows[0]["name"].ToString();
                return c;
            }

        }

        public static bool UpdateZlhqPwd(int userId, string pwd, string userStatus)
        {
            bool flag = false;
            try
            {
                string sSql = string.Empty;

                switch (userStatus.Trim())
                {
                    case "公盘": sSql = "update t_zlhq_t_s_alldept set pwd = '" + DESEncryptor.Encript(pwd) + "' where id = " + Convert.ToString(userId - 1000000);
                        break;
                    case "未入职": sSql = "update t_s_tmp_allusr set pwd = '" + DESEncryptor.Encript(pwd) + "' where id = " + Convert.ToString(userId);
                        break;
                    default: sSql = "update t_s_allusr set pwd = '" + DESEncryptor.Encript(pwd) + "' where id = " + Convert.ToString(userId);
                        break;
                }

                using (_Database db = new _Database("zlhq_user"))
                {
                    db.ExecuteSql(sSql);
                }
                flag = true;
            }
            catch (Exception exception1)
            {
                flag = false;
                throw exception1;
            }
            return flag;
        }

        public static DataSet GetUserListByDeptid(string DeptID)
        {
            if (DeptID.Trim() == "")
            {
                return null;
            }

            String strSql = "select  v.*,t.name as deptname from v_s_usr_lp  as v inner join t_zlhq_t_s_alldept as t on v.t_id=t.Id where v.id is not null and v.t_id = " + DeptID + " and v.status <> '离职' and v.DltFlg=0";

            DataSet ds = new DataSet();
            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(strSql);
            }

            return ds;
        }

        public static DataSet GetEstateUserListByDeptid(string DeptID)
        {
            if (DeptID.Trim() == "")
            {
                return null;
            }

            String strSql = "select * from t_user where dept_id = " + DeptID + " and DltFlag = 0";

            DataSet ds = new DataSet();
            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            return ds;
        }

        public static DataSet GetUserList(string username, string DeptId, string IdList, string UrlDeptID, string condtion)
        {
            if (DeptId.Trim() == "")
            {
                return null;
            }
            string sql = "select * from v_usr_link u where u.status <> '离职' and u.dltflg = 0 ";

            if (username != "")
            {
                sql += " and user_name like  '%" + username + "%' ";
            }

            if (_Utility.GetInfoBySession().m_sRoleID != "1")
            {
                string lastChar = IdList.Substring(IdList.Length - 1, 1);
                if (lastChar.Contains(","))
                    IdList = IdList.Substring(0, IdList.Length - 1);

                if (UrlDeptID == "0")
                {
                    sql += " and u.t_id in (" + IdList + ") ";
                }
                else
                {
                    sql += " and u.t_id in (" + IdList + ") and u.t_id  =" + UrlDeptID + "";
                }
                if (_Utility.GetInfoBySession().m_sRoleID == "29")
                {
                }
            }
            else
            {
                sql += condtion;
            }

            DataSet ds = new DataSet();
            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(sql);
            }

            return ds;
        }

        public static DataTable p_v_usr_link_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_v_usr_link_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataSet GetUserInfoByNo(string no)
        {
            if (no.Trim() == "")
            {
                return null;
            }
            string sql = "select * from t_user where no='" + no + "' order by id desc ";
            DataSet ds = new DataSet();
            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(sql);
            }
            return ds;
        }

        public static void UpdateLoginDate(string id)
        {
            string sql = "update t_user set logindate=getdate() where id =" + id + "";
            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(sql);
            }
        }

        public static string getGetAllChildDeptAllUsers(string deptid)
        {
            string strReturn = "";

            string EHRID_List = ""; string strSql = @" declare @code nvarchar(500) 
                            declare @ID int 
                            set @ID='" + deptid + "'";
            strSql += @"select @code=code from t_zlhq_t_s_alldept where ID =@ID
                            select @code=code from org_unit where ID in(select unitid from t_zldc_deptinfo where ID=@ID)
                            SELECT     h.ID, c.Code, c.EM_ID, c.Name, d.ID AS dept_id, d.Name AS deptName, CASE c.Sex WHEN 1 THEN '男' WHEN 2 THEN '女' END AS sex, 
                                      b.Name AS positionName, e.Status, c.BankAccountNum, CONVERT(varchar(10), f.ReJoinGroupDate, 23) AS ReJoinGroupDate, j.FCardID
                            FROM         dbo.ORG_Position_Employee AS a LEFT OUTER JOIN
                                      dbo.ORG_Position AS b ON a.PositionID = b.ID LEFT OUTER JOIN
                                      dbo.HM_Employees AS c ON c.EM_ID = a.EmID LEFT OUTER JOIN
                                      dbo.ORG_Unit AS d ON d.ID = b.UnitID LEFT OUTER JOIN
                                      dbo.HM_EM_EmpStatus_Code AS e ON e.ID = c.Status LEFT OUTER JOIN
                                      dbo.HM_EM_WorkInfo AS f ON f.EM_ID = c.EM_ID LEFT OUTER JOIN
                                      dbo.HR_ATS_BaseInfo AS j ON j.FEmpID = c.EM_ID INNER JOIN
                                      dbo.t_zldc_EmpInfo AS h ON h.UnitID = c.EM_ID
                            where d.code like @code+'%'";
            DataBaseAll db = new DataBaseAll("ZLDCHR");
            DataTable dt = db.SelectDataBaseAll(strSql).Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i == 0)
                    EHRID_List = "'" + dt.Rows[i]["ID"].ToString() + "'";
                else
                    EHRID_List += ",'" + dt.Rows[i]["ID"].ToString() + "'";
            }

            string sql = "select * from t_user where usr in(" + EHRID_List + ")";

            DataSet ds = new DataSet();
            using (_Database db2 = new _Database("estate"))
            {
                ds = db2.Query(sql);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i == 0)
                        strReturn = dt.Rows[i]["ID"].ToString();
                    else
                        strReturn += "," + dt.Rows[i]["ID"].ToString();
                }
            }

            return strReturn;
        }

        #endregion


    }

}
